package com.lecing.utils;

import com.lecing.dao.StatFcInDao;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Excel 工具类
 * Created by liucs on 2018/5/22.
 */
//@Component
public class ExcelUtil {

    private SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    private StatFcInDao statFcInDao;


    //声明一个该工具类的静态的内部对象
    private static ExcelUtil excelUtil;

    //使用注解@PostConstruct把需要使用的service，dao等加载到上面定义的静态内部对象中
  //  @PostConstruct
    /**
    public void init(){
        excelUtil=this;
    }*/

    /**
     * 读取属性配置文件里指定key对应的值
     * @param key
     * @return
     */
    public static String readPropsByKey(String key) {
        String v ="";
        try {
         //   ExcelCtrl.class.getClassLoader().getResource("./template/月总账.xlsx").getPath()
//            String respath=ExcelUtil.class.getClassLoader().getResource("./tplconfig.properties").getPath();
           // File f=new File("src/main/resources/tplconfig.properties");
            String respath=ExcelUtil.class.getClassLoader().getResource("tplconfig.properties").getPath();
            PropertyResourceBundle prb = new PropertyResourceBundle(new FileInputStream(new File(respath)));
            v = prb.getString(key);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return v;
    }


    /**
     * 导出Excel
     * @param tempPath 模板文件路径
     * @param path 文件路径
     * @param response

     */
    public void exportExcel(String tempPath, String path, HttpServletResponse response){
        File newFile = createNewFile(tempPath, path);
        InputStream is = null;
        XSSFWorkbook workbook = null;
        XSSFSheet sheet = null;
        try {
            is = new FileInputStream(newFile);// 将excel文件转为输入流
            workbook = new XSSFWorkbook(is);// 创建个workbook，

            // 获取第一个sheet
            sheet = workbook.getSheetAt(0);
        }catch (Exception e){
            e.printStackTrace();
        }
        if (sheet!=null) {
            try {
                // 写数据
                FileOutputStream fos = new FileOutputStream(newFile);
                XSSFRow row = sheet.getRow(0);
                if (row == null) {
                    row = sheet.createRow(0);
                }
                XSSFCell cell = row.getCell(0);
                if (cell == null) {
                    cell = row.createCell(0);
                }
                String title = "郑州-乐醒财务日记账";
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                String curDate = sdf.format(new Date());
                cell.setCellValue(title + "(" + curDate + ")");

                workbook.write(fos);
                fos.flush();
                fos.close();

                // 下载
                InputStream fis = new BufferedInputStream(new FileInputStream(newFile));
                byte[] buffer = new byte[fis.available()];
                fis.read(buffer);
                fis.close();
                response.reset();
                response.setContentType("text/html;charset=UTF-8");
                OutputStream toClient = new BufferedOutputStream(
                        response.getOutputStream());
                response.setContentType("application/x-msdownload");
                String newName = URLEncoder.encode(title + ".xlsx",
                        "UTF-8");
                response.addHeader("Content-Disposition",
                        "attachment;filename=\"" + newName + "\"");
                response.addHeader("Content-Length", "" + newFile.length());
                toClient.write(buffer);
                toClient.flush();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (null != is) {
                        is.close();
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        //删除创建的文件
        this.deleteFile(newFile);
    }
    /**
     *根据当前row行，来创建index标记的列数,并赋值数据
     */
    private void createRowAndCell(Object obj, XSSFRow row, XSSFCell cell, int index) {
        cell = row.getCell(index);
        if (cell == null) {
            cell = row.createCell(index);
        }
        if (obj != null)
            cell.setCellValue(obj.toString());
        else
            cell.setCellValue("");
    }


    /**
     * 读取Excel模板，并复制到新文件中供写入和下载
     * @param tempPath 模板文件路径
     * @param rPath
     * @return
     */
    public static File createNewFile(String tempPath, String rPath) {
        // 文件模板路径
        String path = (tempPath);
        File file = new File(path);
        // 保存文件的路径
        String realPath = rPath;
        // 新的文件名
        String newFileName = System.currentTimeMillis() + ".xlsx";
        // 判断路径是否存在
        File dir = new File(realPath);
        if (!dir.exists()) {
            dir.mkdirs();
        }
        // 写入到新的excel
        File newFile = new File(realPath, newFileName);
        try {
            newFile.createNewFile();
            // 复制模板到新文件
            fileChannelCopy(file, newFile);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return newFile;
    }

    /**
     * 复制文件
     * @param file 源文件
     * @param newFile 复制到新的文件
     */
    public static void fileChannelCopy(File file, File newFile) {
        try {
            InputStream in = null;
            OutputStream out = null;
            try {
                in = new BufferedInputStream(new FileInputStream(file), 1024);
                out = new BufferedOutputStream(new FileOutputStream(newFile), 1024);
                byte[] buffer = new byte[1024];
                int len;
                while ((len = in.read(buffer)) != -1) {
                    out.write(buffer, 0, len);
                }
            } finally {
                if (null != in) {
                    in.close();
                }
                if (null != out) {
                    out.close();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
    /**
     * 下载成功后删除
     * @param files
     */
    private void deleteFile(File... files) {
        for (File file : files) {
            if (file.exists()) {
                file.delete();
            }
        }
    }


    /**
     * 插入行
     * @param wb
     * @param sheet
     * @param starRow
     * @param rows
     */
    public static XSSFRow insertRow(Workbook wb, Sheet sheet, int starRow, int rows) {
        sheet.shiftRows(starRow + 1, sheet.getLastRowNum(), rows, true, false);
//  Parameters:
//   startRow - the row to start shifting
//   endRow - the row to end shifting
//   n - the number of rows to shift
//   copyRowHeight - whether to copy the row height during the shift
//   resetOriginalRowHeight - whether to set the original row's height to the default
        starRow = starRow - 1;
        XSSFRow sourceRow=null;
        XSSFRow targetRow=null;
        XSSFCell sourceCell=null;
        XSSFCell targetCell=null;
        for (int i = 0; i < rows; i++) {
            /**
            HSSFRow sourceRow = null;
            HSSFRow targetRow = null;
            HSSFCell sourceCell = null;
            HSSFCell targetCell = null;
             */
            short m;
            starRow = starRow + 1;
            XSSFSheet xssfSheet= (XSSFSheet) sheet;
            sourceRow = xssfSheet.getRow(starRow);

            targetRow=xssfSheet.createRow(starRow+1);
            targetRow.setHeight(sourceRow.getHeight());
            for (m = sourceRow.getFirstCellNum(); m < sourceRow.getLastCellNum(); m++) {
                sourceCell = sourceRow.getCell(m);
                targetCell = targetRow.createCell(m);
                targetCell.setCellStyle(sourceCell.getCellStyle());
                targetCell.setCellType(sourceCell.getCellTypeEnum());
                targetCell.setCellValue(sourceCell.getStringCellValue());
            }
        }
        return targetRow;
    }


}
